package com.zhaoyang.dao;

import com.zhaoyang.model.Course;
import com.zhaoyang.model.Page;
import com.zhaoyang.util.StringUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 赵扬
 * @time 2020/4/27 18:15
 */
public class CourseDao extends BaseDao {

    /**
     * 增加课程
     *
     * @param course
     * @return
     */
    public boolean addCourse(Course course) {
        String sql = "insert into s_course values(null,?,?,?,0,?,?)";
        int cnt = template.update(sql, course.getName(), course.getTeacherId(),
                course.getCourseDate(), course.getMaxNum(), course.getInfo());
        return cnt == 1;
    }

    /**
     * 删除课程
     *
     * @param ids
     * @return
     */
    public boolean deleteCourse(String[] ids) {
        StringBuilder sql = new StringBuilder("delete from s_course where id in (");
        final int len = ids.length;
        for (int i = 0; i < len; i++) {
            if (len - 1 == i) {
                sql.append("?)");
            } else {
                sql.append("?,");
            }
        }
        int cnt = template.update(sql.toString(), (Object) ids);
        return cnt == len;
    }

    /**
     * 修改课程
     *
     * @param course
     * @return
     */
    public boolean editCourse(Course course) {
        String sql = "update s_course set name = ? ,teacher_id = ?,course_date = ?," +
                "max_num = ? ,info = ? where id = ?";

        int cnt = template.update(sql, course.getName(), course.getTeacherId(), course.getCourseDate(),
                course.getMaxNum(), course.getInfo(), course.getId());
        return cnt == 1;
    }

    /**
     * 获取课程列表
     *
     * @param course
     * @param page
     * @return
     */
    public List<Course> getCourseList(Course course, Page page) {
        List<Course> result = new ArrayList<Course>();
        String sql = "select * from s_course ";
        if (!StringUtil.isEmpty(course.getName())) {
            sql += "and name like '%" + course.getName() + "%'";
        }
        if (course.getTeacherId() != 0) {
            sql += " and teacher_id = " + course.getTeacherId() + "";
        }
        sql += " limit " + page.getStart() + "," + page.getPageSize();
        ResultSet resultSet = query(sql.replaceFirst("and", "where"));
        try {
            while (resultSet.next()) {
                Course cl = new Course();
                cl.setId(resultSet.getInt("id"));
                cl.setName(resultSet.getString("name"));
                cl.setTeacherId(resultSet.getInt("teacher_id"));
                cl.setCourseDate(resultSet.getString("course_date"));
                cl.setSelectedNum(resultSet.getInt("selected_num"));
                cl.setMaxNum(resultSet.getInt("max_num"));
                cl.setInfo(resultSet.getString("info"));
                result.add(cl);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * @param course 课程实体
     * @return 总课程数
     */
    public long getCourseListTotal(Course course) {
        int total = 0;
        String sql = "select count(*)as total from s_course ";
        if (!StringUtil.isEmpty(course.getName())) {
            sql += "and name like '%" + course.getName() + "%'";
        }
        if (course.getTeacherId() != 0) {
            sql += " and teacher_id = " + course.getTeacherId() + "";
        }
        ResultSet resultSet = query(sql.replaceFirst("and", "where"));
        try {
            while (resultSet.next()) {
                total = resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    /**
     * 查看课程是否选满
     *
     * @param courseId
     * @return
     */
    public boolean isFull(int courseId) {
        String sql = "select * from s_course where selected_num >= max_num and id = ?";
        List<Course> courses = template.query(sql, new BeanPropertyRowMapper<Course>(Course.class), courseId);

        if (0 == courses.size()) {
            return false;
        }
        return true;
    }

    /**
     * 更新选课人数
     *
     * @param courseId 课程编号
     * @param num      选课人数
     * @return 是否更新成功
     */
    public boolean updateCourseSelectedNum(int courseId, int num) {
        // 选课 人数为 0
        if (0 == num) {
            return true;
        }
        // 如果课已经满了 返回false
        if (isFull(courseId)) {
            return false;
        }
        // 查询剩余人数
        String str = "select max_num - selected_num from s_course where id = ?";
        Integer res = template.queryForObject(str, Integer.class, courseId);
        // 剩余人数小于num 返回false
        if (res < num) {
            return false;
        }

        String sql = "update s_course set selected_num = selected_num + ? where id = ?";
        int cnt = template.update(sql, num, courseId);
        return cnt == 1;
    }

    /**
     * 获取指定id范围内的课程列表
     *
     * @param ids
     * @return
     */
    public List<Course> getCourse(int[] ids) {
        List<Course> courses = null;
        if (ids.length == 0) {
            return courses;
        }
        final int len = ids.length;

        StringBuilder sql = new StringBuilder("select * from s_course where id");

        if (1 == len) {
            sql.append(" = " + ids[0]);
        } else {
            sql.append(" in (");

            for (int i = 0; i < len - 1; i++) {
                sql.append(ids[i] + ",");
            }
            sql.append(ids[len - 1] + ")");
        }
        courses = new ArrayList<Course>();
        ResultSet resultSet = query(sql.toString());
        try {
            while (resultSet.next()) {
                Course course = new Course();
                course.setId(resultSet.getInt("id"));
                course.setName(resultSet.getString("name"));
                course.setTeacherId(resultSet.getInt("teacher_id"));
                course.setCourseDate(resultSet.getString("course_date"));
                course.setMaxNum(resultSet.getInt("max_num"));
                course.setSelectedNum(resultSet.getInt("selected_num"));
                course.setInfo(resultSet.getString("info"));
                courses.add(course);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return courses;
    }

    /**
     * 获取指定的课程
     *
     * @param id 课程编号
     * @return 课程实体
     */
    public Course getCourseById(int id) {
        String sql = "select * from s_course where id = ?";
        List<Course> courses = template.query(sql, new BeanPropertyRowMapper<Course>(Course.class), id);
        if (0 == courses.size()) {
            return null;
        }
        return courses.get(0);
    }

}
